#!/usr/bin/env python
'''
Database connection handler
'''

import os
import sys
from pysqlite2 import dbapi2 as sqlite
from pysqlite2.dbapi2 import IntegrityError

class ExistingError(Exception):
    pass

class DB:
    def __init__(self):
        '''Connect to the database'''
        self.connection = sqlite.connect('coasters.db')
        self.cursor = self.connection.cursor()

    def addBrewery(self, name, owner=None, country="N/A", state=None, city=None):
        '''Add a new brewery'''
        try:
            self.cursor.execute("INSERT INTO breweries (name, owner, country, state, city) VALUES (?, ?, ?, ?, ?)",
                    (name, owner, country, state, city, ))
            self.connection.commit()
        except IntegrityError:
            raise ExistingError

    def updateBrewery(self, oldname, name, owner=None, country="N/A", state=None, city=None):
        '''Updates info about an existing brewery'''
        try:
            self.cursor.execute("UPDATE breweries SET name=?, owner=?, country=?, state=?, city=? WHERE name LIKE ?",
                    (name, owner, country, state, city, oldname, ))
            self.cursor.execute("UPDATE brands SET brewery=? WHERE brewery LIKE ?",
                    (name, oldname, ))
            self.connection.commit()
        except IntegrityError:
            raise ExistingError

    def deleteBrewery(self, name):
        '''Delete a brewery'''
        self.cursor.execute("SELECT name FROM brands WHERE brewery LIKE ?", (name, ))
        brands = self.cursor.fetchone()

        self.cursor.execute("DELETE FROM breweries WHERE name LIKE ?", (name, ))
        self.cursor.execute("DELETE FROM brands WHERE brewery LIKE ?", (name, ))
        if (brands != None):
            for brand_name in brands:
                self.cursor.execute("DELETE FROM coasters WHERE brand LIKE ?", (brand_name, ))

        self.connection.commit()

    def getAllBreweries(self, filter='%'):
        '''Returns all the breweries (you can specify a sql wildcard
        as filter'''
        if ((len(filter) == 0) or (filter == '%%')):
            filter = '%'
        sql = "SELECT name FROM breweries WHERE name LIKE '%s' ORDER BY name ASC" % (filter, )
        self.cursor.execute(sql)
        # I'm still trying to figuring out why this query returns a tuple
        return self.cursor.fetchall()

    def getAllBreweriesInfo(self, filter='%'):
        '''Returns all the breweries'informations available (you can specify a sql wildcard
        as filter'''
        if ((len(filter) == 0) or (filter == '%%')):
            filter = '%'
        self.cursor.execute("SELECT name, owner, country, state, city FROM breweries WHERE name LIKE ? ORDER BY NAME ASC", (filter, ))
        return self.cursor.fetchall()

    def getBreweryByBrand(self, brand):
        '''Returns the brewery for a certain brand'''
        self.cursor.execute("SELECT brewery FROM brands WHERE name LIKE ?", (brand, ))
        return self.cursor.fetchone()[0]

    def getBrandsByBrewery(self, brewery):
        '''Returns a list of brands for a certain brewery'''
        self.cursor.execute("SELECT name FROM brands WHERE brewery LIKE ?", (brewery, ))
        return self.cursor.fetchall()

    def getCoastersNumByBrewery(self, brewery):
        '''Scan for brands in brewery and returns the total coaster's number'''
        brands = self.getBrandsByBrewery(brewery)
        total = 0
        if (brands != None):
            for brand in brands:
                total = total + int(self.getCoasterNum(brand[0]))

        return total

    def getAllBrands(self, filter = '%'):
        '''Return all the brands inserted in the db'''
        if ((len(filter) == 0) or (filter == '%%')):
            filter = '%'
        sql = "SELECT name FROM brands WHERE name LIKE '%s' ORDER BY name ASC" % (filter, )

        self.cursor.execute(sql)
        return self.cursor.fetchall()

    def addBrand(self, name, brewery, comment):
        '''Add a new brand'''
        try:
            self.cursor.execute("INSERT INTO brands (name, brewery, cmt) VALUES (?, ?, ?)", (name, brewery, comment, ))
            self.connection.commit()
        except IntegrityError:
            raise ExistingError

    def delBrand(self, name):
        '''Delete a brand'''
        self.cursor.execute("DELETE FROM brands WHERE name LIKE ?", (name, ))
        self.connection.commit()

    def updateBrand(self, orig_name, name, brewery, comment):
        '''Update the brand informations'''
        obj = ((name, brewery, comment, orig_name, ))
        obj2 = ((name, orig_name))
        self.cursor.execute("UPDATE brands SET name=?, brewery=?, cmt=? WHERE name LIKE ?", obj)
        self.cursor.execute("UPDATE coasters SET brand=? where BRAND LIKE ?", obj2)
        self.connection.commit()

    def addCoaster(self, brand, front_filename, back_filename, comment):
        if (not os.path.exists(front_filename)):
            raise FileException
        if ((back_filename != None) and (not os.path.exists(back_filename))):
            raise FileException

        blob_front = open(front_filename, 'rb').read()
        if (back_filename == None):
            obj = ((brand, sqlite.Binary(blob_front), None, comment))
        else:
            blob_back = open(back_filename, 'rb').read()
            obj = ((brand, sqlite.Binary(blob_front), sqlite.Binary(blob_back), comment))

        self.cursor.execute("INSERT INTO coasters (brand, photo_front, photo_back, cmt) VALUES (?, ?, ?, ?)", obj)
        self.connection.commit()

    def delCoaster(self, id):
        '''Delete a coaster'''
        self.cursor.execute("DELETE FROM coasters WHERE id=?", (id, ))
        self.connection.commit()

    def updateCoaster(self, id, brand, front_filename, back_filename, comment):
        '''Update the coasters informations'''
        if ((front_filename != None) and (not os.path.exists(front_filename))):
            raise FileException
        if ((back_filename != None) and (not os.path.exists(back_filename))):
            raise FileException

        # the best thing to keep things in order is to do separate queries
        # first update brand and comment:
        self.cursor.execute("UPDATE coasters SET brand=?, cmt=? WHERE id=?", (brand, comment, id, ))

        # then check if front and back are specified and update them if needed
        if (front_filename != None):
            blob_front = open(front_filename, 'rb').read()
            self.cursor.execute("UPDATE coasters SET photo_front=? WHERE id=?", (sqlite.Binary(blob_front), id, ))

        if (back_filename != None):
            blob_back = open(back_filename, 'rb').read()
            self.cursor.execute("UPDATE coasters SET photo_back=? WHERE id=?", (sqlite.Binary(blob_back), id, ))

        # and finally commit the changes
        self.connection.commit()

    def getAllCoasters(self, filter = '%'):
        '''Return all the coasters inserted in the db'''
        if ((len(filter) == 0) or (filter == '%%')):
            filter = '%'

        self.cursor.execute("SELECT id, brand FROM coasters WHERE brand LIKE ? ORDER BY brand ASC", (filter ,))
        return self.cursor.fetchall()

    def getFrontCoasterImg(self, id):
        self.cursor.execute("SELECT photo_front FROM coasters WHERE id = ?", (id,))
        img = self.cursor.fetchone()[0]
        return img

    def getBackCoasterImg(self, id):
        self.cursor.execute("SELECT photo_back FROM coasters WHERE id = ?", (id,))
        img = self.cursor.fetchone()[0]
        return img

    def getCoasterNum(self, brand=None):
        '''
        Returns the number of coasters in archive for the specified brand.
        If brand is not specified, then it will return the total coaster's
        number
        '''
        if (brand == None):
            brand = '%'
        self.cursor.execute("SELECT COUNT(*) FROM coasters WHERE brand LIKE ?", (brand, ))
        return self.cursor.fetchone()[0]

    def getBrandComment(self, brand):
        '''Get the brand's comment'''
        self.cursor.execute("SELECT cmt FROM brands WHERE name LIKE ?", (brand, ))
        return self.cursor.fetchone()[0]

    def getBrandCountry(self, brand):
        '''Get the brand's country'''
        # first take the brands'brewery
        self.cursor.execute("SELECT brewery FROM brands WHERE name LIKE ?", (brand, ))
        brewery = self.cursor.fetchone()[0]
        self.cursor.execute("SELECT country FROM breweries WHERE name LIKE ?", (brewery, ))
        return self.cursor.fetchone()[0]

    def getCoasterComment(self, id):
        '''Get the coaster's comment'''
        self.cursor.execute("SELECT cmt FROM coasters WHERE id=?", (id, ))
        return self.cursor.fetchone()[0]

    def getCoasterBrand(self, id):
        '''Get the coaster's brand'''
        self.cursor.execute("SELECT brand FROM coasters WHERE id=?", (id, ))
        return self.cursor.fetchone()[0]
